import sqlite3
import os
from typing import List, Dict, Optional, Any
from database_config import CURRENT_DB_CONFIG

class DatabaseManager:
    """统一数据库管理器，支持多种数据库类型"""
    
    def __init__(self):
        self.config = CURRENT_DB_CONFIG
        self.db_type = self.config["type"]
        self.connection = None
        self.init_database()
    
    def get_connection(self):
        """获取数据库连接"""
        if self.db_type == "sqlite":
            return sqlite3.connect(self.config["path"])
        
        elif self.db_type == "mysql":
            try:
                import mysql.connector
                return mysql.connector.connect(
                    host=self.config["host"],
                    port=self.config["port"],
                    database=self.config["database"],
                    user=self.config["username"],
                    password=self.config["password"],
                    charset=self.config["charset"]
                )
            except ImportError:
                raise ImportError("请安装mysql-connector-python: pip install mysql-connector-python")
        
        elif self.db_type == "postgresql":
            try:
                import psycopg2
                return psycopg2.connect(
                    host=self.config["host"],
                    port=self.config["port"],
                    database=self.config["database"],
                    user=self.config["username"],
                    password=self.config["password"],
                    sslmode=self.config.get("sslmode", "prefer")
                )
            except ImportError:
                raise ImportError("请安装psycopg2: pip install psycopg2-binary")
        
        else:
            raise ValueError(f"不支持的数据库类型: {self.db_type}")
    
    def init_database(self):
        """初始化数据库表"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 根据数据库类型调整SQL语句
        if self.db_type == "sqlite":
            create_table_sql = '''
                CREATE TABLE IF NOT EXISTS arbitration_cases (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    case_number TEXT UNIQUE NOT NULL,
                    title TEXT NOT NULL,
                    status TEXT NOT NULL,
                    plaintiff TEXT NOT NULL,
                    defendant TEXT NOT NULL,
                    amount REAL,
                    created_date TEXT,
                    updated_date TEXT,
                    description TEXT
                )
            '''
        elif self.db_type == "mysql":
            create_table_sql = '''
                CREATE TABLE IF NOT EXISTS arbitration_cases (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    case_number VARCHAR(255) UNIQUE NOT NULL,
                    title VARCHAR(500) NOT NULL,
                    status VARCHAR(100) NOT NULL,
                    plaintiff VARCHAR(255) NOT NULL,
                    defendant VARCHAR(255) NOT NULL,
                    amount DECIMAL(15,2),
                    created_date DATETIME,
                    updated_date DATETIME,
                    description TEXT
                )
            '''
        elif self.db_type == "postgresql":
            create_table_sql = '''
                CREATE TABLE IF NOT EXISTS arbitration_cases (
                    id SERIAL PRIMARY KEY,
                    case_number VARCHAR(255) UNIQUE NOT NULL,
                    title VARCHAR(500) NOT NULL,
                    status VARCHAR(100) NOT NULL,
                    plaintiff VARCHAR(255) NOT NULL,
                    defendant VARCHAR(255) NOT NULL,
                    amount DECIMAL(15,2),
                    created_date TIMESTAMP,
                    updated_date TIMESTAMP,
                    description TEXT
                )
            '''
        
        cursor.execute(create_table_sql)
        
        # 插入示例数据
        sample_cases = [
            ("ARB2024001", "商务合同纠纷", "进行中", "公司A", "公司B", 100000.00, "2024-01-15", "2024-01-20", "关于软件开发合同的争议"),
            ("ARB2024002", "劳动争议", "已结案", "员工张三", "某公司", 50000.00, "2024-02-01", "2024-02-15", "关于加班费的争议"),
            ("ARB2024003", "货物买卖纠纷", "可用", "贸易公司C", "制造商D", 200000.00, "2024-03-01", "2024-03-01", "关于货物质量的争议"),
        ]
        
        # 根据数据库类型调整插入语句
        if self.db_type == "sqlite":
            insert_sql = '''
                INSERT OR IGNORE INTO arbitration_cases 
                (case_number, title, status, plaintiff, defendant, amount, created_date, updated_date, description)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            '''
        else:  # MySQL和PostgreSQL
            insert_sql = '''
                INSERT IGNORE INTO arbitration_cases 
                (case_number, title, status, plaintiff, defendant, amount, created_date, updated_date, description)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            '''
        
        try:
            cursor.executemany(insert_sql, sample_cases)
        except:
            # 如果INSERT IGNORE不支持，逐条插入并忽略错误
            for case in sample_cases:
                try:
                    if self.db_type == "sqlite":
                        cursor.execute(insert_sql, case)
                    else:
                        cursor.execute(insert_sql.replace("INSERT IGNORE", "INSERT"), case)
                except:
                    pass  # 忽略重复插入错误
        
        conn.commit()
        conn.close()
    
    def execute_query(self, sql: str, params: tuple = None) -> List[Dict]:
        """执行查询并返回结果"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        if params:
            # 根据数据库类型调整参数占位符
            if self.db_type != "sqlite" and "?" in sql:
                sql = sql.replace("?", "%s")
            cursor.execute(sql, params)
        else:
            cursor.execute(sql)
        
        rows = cursor.fetchall()
        
        # 获取列名
        if self.db_type == "sqlite":
            columns = [description[0] for description in cursor.description]
        else:
            columns = [description[0] for description in cursor.description]
        
        conn.close()
        
        # 转换为字典格式
        return [dict(zip(columns, row)) for row in rows]
    
    def query_cases(self, status: Optional[str] = None) -> List[Dict]:
        """查询仲裁案件"""
        if status:
            return self.execute_query(
                'SELECT * FROM arbitration_cases WHERE status = ?', 
                (status,)
            )
        else:
            return self.execute_query('SELECT * FROM arbitration_cases')
    
    def get_available_cases(self) -> List[Dict]:
        """获取可用的案件"""
        return self.query_cases(status="可用")
    
    def search_cases(self, keyword: str) -> List[Dict]:
        """搜索案件"""
        if self.db_type == "sqlite":
            sql = '''
                SELECT * FROM arbitration_cases 
                WHERE title LIKE ? OR description LIKE ? OR case_number LIKE ?
            '''
        else:
            sql = '''
                SELECT * FROM arbitration_cases 
                WHERE title LIKE %s OR description LIKE %s OR case_number LIKE %s
            '''
        
        search_pattern = f'%{keyword}%'
        return self.execute_query(sql, (search_pattern, search_pattern, search_pattern))

# 保持向后兼容
class ArbitrationDB(DatabaseManager):
    """向后兼容的类名"""
    pass
